package com.tjsinfo.tjsoa.bylaws.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import javax.sql.DataSource;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import org.springframework.jdbc.object.MappingSqlQuery;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import com.tjsinfo.tjsoa.bylaws.vo.Bylaws;

public class BylawsDao extends JdbcDaoSupport implements IBylawsDao{



	public String save(final Bylaws tjb) {
		if (tjb.getId() == null) {
			// KeyHolder keyHolder = new GeneratedKeyHolder();
			final String sql = "insert into dbo.t_bylaws(id,cid,title,pic,userId,createDate,state,resume,content,auditMan,auditIdea,auditing_sign, wenzhang,flowid) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
			String uuid1 = UUID.randomUUID().toString();
			uuid1 = uuid1.substring(0,8)+uuid1.substring(9,13)+uuid1.substring(14,18)+uuid1.substring(19,23)+uuid1.substring(24); 
			final String uuid =uuid1;
			getJdbcTemplate().update(new PreparedStatementCreator() {
				public java.sql.PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
					int i = 0;
					java.sql.PreparedStatement ps = conn.prepareStatement(sql,new String[] { "id" });
					ps = conn.prepareStatement(sql);
					ps.setString(++i, uuid);
					ps.setString(++i,tjb.getCid());
					ps.setString(++i,tjb.getTitle());
					ps.setString(++i,tjb.getPic());
					ps.setString(++i,tjb.getUserId());
					ps.setString(++i,tjb.getCreateDate());
					ps.setString(++i,tjb.getState());
					ps.setString(++i,tjb.getResume());
					ps.setString(++i,tjb.getContent());
					ps.setString(++i,tjb.getAuditMan());
					ps.setString(++i,tjb.getAuditIdea());
					ps.setString(++i,tjb.getAuditing_sign());
					ps.setString(++i,tjb.getWenzhang());
					ps.setInt(++i, tjb.getFlowid());
					return ps;					
				}
			});
			
			return uuid;
      
			
		} else {
			
			getJdbcTemplate().update(
							"update dbo.t_bylaws set id = ?,cid = ?, title = ?, pic = ?, userId = ?, createDate = ?, state = ?, resume = ?," +
					" content = ?, auditMan = ?, auditIdea = ?, auditing_sign = ?,wenzhang = ? where id = ?",
							new Object[] { tjb.getId(),tjb.getCid(), tjb.getTitle(),tjb.getPic(),
									 tjb.getUserId(), tjb.getCreateDate(),tjb.getState(),
									 tjb.getResume(), tjb.getContent(),tjb.getAuditMan(),
									 tjb.getAuditIdea(),tjb.getAuditing_sign(),tjb.getWenzhang(),
									 tjb.getId() });
			return "0";
		}
		
	}
	private Bylaws help(ResultSet rs,Bylaws article) throws SQLException{
		article.setId(new String(rs.getString("id")));
		article.setCid(rs.getString("cid"));
		article.setTitle(rs.getString("title"));
		article.setPic(rs.getString("pic"));
		article.setUserId(rs.getString("userId"));
		article.setCreateDate(rs.getString("createDate"));
		article.setState(rs.getString("state"));
		article.setAuditMan(rs.getString("auditMan"));
		article.setContent(rs.getString("content"));
		article.setResume(rs.getString("resume"));
		article.setAuditIdea(rs.getString("auditIdea"));
		article.setAuditing_sign(rs.getString("auditing_sign"));
		article.setWenzhang(rs.getString("wenzhang"));
		
		return article;
	}

		
	public void removeArticle(String articleId) {		
		getJdbcTemplate().update("delete from dbo.t_bylaws where id = ? ",new Object[]{ articleId});
	} 
	
	public List findAll() {
		List list = new ArrayList();
		list = new BzzfQuery(getDataSource()).execute();
		return list;
	}

	public List findByProperty(String propertyName, String value) {
		List list = new ArrayList();
		String sql = "select * from dbo.t_bylaws where " + propertyName
				+ " = ? order by state desc,id desc";
		int types = Types.VARCHAR;
		list = new BzzfQuery(getDataSource(), sql, value, types)
				.execute(new Object[] { value });
		return list;
	}

	public List findByTwoProperty(String propertyName1, String value1,
			String propertyName2, String value2) {
		List list = new ArrayList();
		String sql = "select * from dbo.t_bylaws where " + propertyName1
				+ " = ? and " + propertyName2 + "= ? order by state desc,createDate desc";
		int types = Types.VARCHAR;
		list = new BzzfQuery(getDataSource(), sql, value1, types, value2, types)
				.execute(new Object[] { value1, value2 });
		return list;
	}
	
	public List queryResult(String propertyName1, String value1,
			String propertyName2, String value2) {
		List list = new ArrayList();
		String sql = "select * from dbo.t_bylaws where " + propertyName1
				+ " = ? and " + propertyName2 + " like ? ";
		System.out.println("select * from dbo.t_bylaws where " + propertyName1
				+ " = "+value1+" and " + propertyName2  + " like "+value2);
		int types = Types.VARCHAR;
		list = new BzzfQuery(getDataSource(), sql, value1, types, value2, types)
				.execute(new Object[] { value1, value2 });
		return list;
	}

	public List findByThreeProperty(String propertyName1, String value1,
			String propertyName2, String value2, String propertyName3,
			String value3) {
		List list = new ArrayList();
		String sql = "select * from dbo.t_bylaws where " + propertyName1
				+ " = ? and " + propertyName2 + "= ? and " + propertyName3 + "= ? order by state desc,createDate desc";
		int types = Types.VARCHAR;
		list = new BzzfQuery(getDataSource(), sql, value1, types, value2, types, value3, types)
				.execute(new Object[] { value1, value2, value3 });
		return list;
	}



	
	public List findList(String value, String value1 ){
	List list = new ArrayList();
	String sql = "select  top "+ value+" * from dbo.t_bylaws where cid "
			+ " = ?  and auditing_sign = 1 order by state desc,id desc";
	int types = Types.VARCHAR;
	list = new BzzfQuery(getDataSource(), sql, value1, types)
			.execute(new Object[] { value1 });
	return list;

	}
	
	
	class BzzfQuery extends MappingSqlQuery {

		BzzfQuery(DataSource ds) {
			super(ds, "select * from dbo.t_bylaws order by state desc,id desc");
			compile();
		}

		BzzfQuery(DataSource ds, String sql, String column, int types) {
			super(ds, sql);
			super.declareParameter(new SqlParameter(column, types));
			compile();
		}

		BzzfQuery(DataSource ds, String sql, String column1, int types1,
				String column2, int types2) {
			super(ds, sql);
			super.declareParameter(new SqlParameter(column1, types1));
			super.declareParameter(new SqlParameter(column2, types2));
			compile();
		}

		BzzfQuery(DataSource ds, String sql, String column1, int types1,
				String column2, int types2, String column3, int types3) {
			super(ds, sql);
			super.declareParameter(new SqlParameter(column1, types1));
			super.declareParameter(new SqlParameter(column2, types2));
			super.declareParameter(new SqlParameter(column3, types3));
			compile();
		}

		BzzfQuery(DataSource ds, String sql, String column1, int types1,
				String column2, int types2, String column3, int types3,
				String column4, int types4) {
			super(ds, sql);
			super.declareParameter(new SqlParameter(column1, types1));
			super.declareParameter(new SqlParameter(column2, types2));
			super.declareParameter(new SqlParameter(column3, types3));
			super.declareParameter(new SqlParameter(column4, types4));
			compile();
		}

		protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			Bylaws hbgb = new Bylaws();
			hbgb = help(rs, hbgb);
			return hbgb;
		}
	}






}
